--[[

    Filename:	auxsql.lua

    Programmer:	aesiot

    Remark:

--]]

local SQLite = require( "SQLite3" )
local auxlib = require( "auxlib"  )

local auxsql = { _VERSION = "1.0" }

--------------------------------------------------------------------------------

function auxsql.create_table( db, tbname, ... )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	local params = ''

	for index, value in ipairs( { ... } ) do

		if 1 == index then

			params = value
		else

			params = params .. ", " .. value
		end
	end

	return db:exec( string.format( "CREATE TABLE %s ( %s )", tbname, params ) )
end

--------------------------------------------------------------------------------

function auxsql.delete_from( db, tbname, condition )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	auxlib.argcheck( auxlib.is_none( condition ) or auxlib.is_string( condition ), 3, "(string expected)" )

	local where = ''

	if condition then

		where = " WHERE " .. condition
	end

	return db:exec( string.format( "DELETE FROM %s%s", tbname, where ) )
end

--------------------------------------------------------------------------------

function auxsql.drop_table( db, tbname )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	return db:exec( string.format( "DROP TABLE %s", tbname ) )
end

--------------------------------------------------------------------------------

function auxsql.escape_sq( s )

	if not auxlib.is_string( s ) then

		return s
	end

	local result = ''

	for i = 1, #s do

		local c = string.char( string.byte( s, i ) )

		if '\'' == c then

			result = result .. "''"
		else

			result = result .. c
		end
	end

	return result
end

--------------------------------------------------------------------------------

function auxsql.gen_field( tbname, kind, default, not_null, unique, primary_key, autoincrement )

	local field = tbname .. ' ' .. string.upper( kind )

	if not auxlib.is_none( default ) then

		field = field .. string.format( " DEFAULT '%s'", auxsql.escape_sq( default ) )
	end

	if auxlib.is_boolean( not_null ) and not_null then

		field = field .. " NOT NULL"
	else

		field = field .. " NULL"
	end

	if auxlib.is_boolean( unique ) and unique then

		field = field .. " UNIQUE"
	end

	if auxlib.is_boolean( primary_key ) and primary_key then

		field = field .. " PRIMARY KEY"
	end

	if auxlib.is_boolean( autoincrement ) and autoincrement then

		field = field .. " AUTOINCREMENT"
	end

	return field
end

--------------------------------------------------------------------------------

local function gen_value( v )

	if auxlib.is_string( v ) then

		return "'" .. auxsql.escape_sq( v ) .. "'"

	elseif null == v then

		return "NULL"
	else

		return tostring( v )
	end
end

function auxsql.insert_into( db, tbname, columns, ... )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	auxlib.argcheck( auxlib.is_none( columns ) or auxlib.is_table( columns ), 4, "(table expected)" )

	local fields, values = '', ''

	if columns then

		for i, v in ipairs( columns ) do

			if 1 == i then

				fields = fields .. '(' .. v
			else

				fields = fields .. ',' .. v
			end
		end

		if 0 ~= #fields then

			fields = fields .. ')'
		end
	end

	for i, v in ipairs( { ... } ) do

		if 1 == i then

			values = gen_value( v )
		else

			values = values .. ',' .. gen_value( v )
		end
	end

	return db:exec( string.format( "INSERT INTO %s%s VALUES (%s)", tbname, fields, values ) )
end

--------------------------------------------------------------------------------

function auxsql.is_table_exist( db, tbname )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	local exist = false

	local r, e = db:exec( string.format( "SELECT COUNT(*) AS total FROM SQLITE_MASTER WHERE type='table' AND name='%s'", tbname ), function( result )

		exist = ( math.tointeger( result.total ) ~= 0 )
	end )

	return r and exist
end

--------------------------------------------------------------------------------

function auxsql.open_database( path )

	if auxlib.is_none( path ) then

		path = ":memory:"

	elseif not auxlib.is_regular_file( path ) then

		local b, r, m = pcall( io.open, path, "wb" )

		if not b then

			return r, m
		end

		r:close()
	end

	return SQLite.open( path )
end

--------------------------------------------------------------------------------

function auxsql.update( db, tbname, values, condition )

	auxlib.argcheck( auxlib.is_userdata( db ), 1, "(userdata expected)" )

	auxlib.argcheck( auxlib.is_string( tbname ), 2, "(string expected)" )

	auxlib.argcheck( auxlib.is_table( values ), 3, "(table expected)" )

	auxlib.argcheck( auxlib.is_none( condition ) or auxlib.is_string( condition ), 4, "(string expected)" )

	local which, where, first = '', '', true

	for k, v in pairs( values ) do

		if first then

			first = false

			which = which .. string.format( "%s=%s", k, gen_value( v ) )
		else

			which = which .. string.format( ",%s=%s", k, gen_value( v ) )
		end
	end

	if condition then

		where = " WHERE " .. condition
	end

	return db:exec( string.format( "UPDATE %s SET %s%s", tbname, which, where ) )
end

--------------------------------------------------------------------------------

return auxsql
